/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package services;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import common.Configuration;

import models.UserDTO;

/**
*
* @author Sami Diab
* @since 	23/10/2011
* Determines the functions that modify the User table
*/
public class UserServiceImpl implements IEntity {

    Statement stmt = null;
    ResultSet rst = null;
    private UserDTO user;
    private List<UserDTO> userList;

    public UserDTO GetById(int id) {
        try {
        	
            stmt = Configuration.GetStatement();
            rst = stmt.executeQuery("select * from user where idUser=" + id);
            user.setidUser(id);
            user.setLogin(rst.getString("username"));
            user.setLastName(rst.getString("LastName"));
            user.setFirstName(rst.getString("FirstName"));
            user.setEmail(rst.getString("Email"));
            user.setPassword(rst.getString("password"));
            user.setIdRole(rst.getInt("idRole"));
            Configuration.CloseConnection();
            
        } catch (SQLException ex) {
            Logger.getLogger(UserServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return user;
    }

    public UserDTO GetByLogin(String name) {
        UserDTO u = new UserDTO();
    	try {
    		
            stmt = Configuration.GetStatement();
            rst = stmt.executeQuery("select * from user where login='" + name + "'");
            rst.first();
            u.setidUser(rst.getInt("idUser"));
            u.setLogin(rst.getString("login"));
            u.setLastName(rst.getString("LastName"));
            u.setFirstName(rst.getString("FirstName"));
            u.setEmail(rst.getString("Email"));
            u.setPassword(rst.getString("password"));
            u.setIdRole(rst.getInt("idRole"));
            Configuration.CloseConnection();
            
        } catch (SQLException ex) {
            Logger.getLogger(UserServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return u;
    }
    
    public List<UserDTO> GetAll() {
        try {
        	
            stmt = Configuration.GetStatement();
            rst = stmt.executeQuery("select * from user ");
            while (rst.next()) {
                user.setidUser(rst.getInt("idUser"));
                user.setLogin(rst.getString("username"));
                user.setLastName(rst.getString("LastName"));
                user.setFirstName(rst.getString("FirstName"));
                user.setEmail(rst.getString("Email"));
                user.setPassword(rst.getString("password"));
                user.setIdRole(rst.getInt("idRole"));
                userList.add(user);
            }
            Configuration.CloseConnection();
            
        } catch (SQLException ex) {
            Logger.getLogger(UserServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return userList;
    }

    public void Add(Object obj){
    	try {
    		
    		UserDTO objCast=(UserDTO)obj;
    		stmt = Configuration.GetStatement();
    		stmt.execute("INSERT INTO dbsurvey.user (`idUser`, userName, `LastName`, `FirstName`, `Email`, password, `idRole`)  VALUES (" + objCast.getidUser() + "," + objCast.getLogin() + "," + objCast.getLastName() + "," + objCast.getFirstName() + "," + objCast.getEmail() + "," + objCast.getPassword() + "," + objCast.getIdRole() + ")");
    		Configuration.CloseConnection();
    		
    	} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }

    public void Update(Object obj){
    	
    	try {
    		
    		UserDTO objCast=(UserDTO)obj;
    		stmt = Configuration.GetStatement();
    		stmt.execute("update dbsurvey.user set  username=" + objCast.getLogin() + "and LastName=" + objCast.getLastName() + "FirstName=" + objCast.getFirstName() + "Email=" + objCast.getEmail() + "password=" + objCast.getPassword() + "idRole=" + objCast.getIdRole() + " where idUser=" + objCast.getidUser());
    		Configuration.CloseConnection();
    		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }

    public void Remove(int id){
        try {
        	
        	stmt = Configuration.GetStatement();
        	stmt.execute("delete from user where idUser =" + id);
        	Configuration.CloseConnection();
        
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
}
